| ## Charlie Calvert's C++ Builder Unleashed ## - 11 - ## Working with Field Objects This chapter covers a set of visual tools you can use to simplify database development. The major areas covered are as follows: The Fields Editor `TField` descendant objects Calculated fields The `TDBGrid` component Lookup fields `MultiRecord` objects--`TDBCtrlGrid` These subjects are all related to databases, and to the `TField` and `TDBGrid` objects in particular. BCB is a very sophisticated database tool, and it takes time to get a feeling for the breadth and depth of the tools it provides for client/server developers. One of the goals of this chapter is to give you some sense of the powerful `TField` object and its impact on designing database applications. One of the most frequently mentioned tools in this chapter is the Fields Editor. By using the Fields Editor, you can create objects that influence the appearance of data shown in visual controls such as `TDBEdit` and `TDBGrid`. For instance, you can use the objects made in the Fields Editor to format data so that it appears as currency or as a floating-point number with a defined precision. These same changes can be accomplished through the Data Dictionary in the Database Explorer or through the Database Desktop. These latter tools, however, have a global impact on the field's potential values, whereas the changes made in the Object Inspector affect only the current application. If you want to dynamically emphasize a particular column or row in a table, this is the chapter you should read to get the information you need. For instance, this chapter covers the `Columns` property of the `TDBGrid` control. The `Columns` property can be used to change the appearance of a grid so that its columns are arranged in a new order or are hidden. You can also use the `Columns` property to change the color of columns in a grid, or to insert drop-down combo boxes into a grid. The lessons you learn in this chapter will arm you with key techniques used by most pro- grammers when they present database tables to their users. Much of the material involves manipulating visual tools, but the basic subject matter is fairly technical and assumes a basic understanding of the BCB environment and language. Note that third parties such as TurboPower (`www.turbopower.com`) and Woll2Woll (`www.woll2woll.com`) have grid objects that go beyond the capability of the native grids that ship with BCB. ### The Fields Editor To get things rolling, it might be a good idea to take a close look at the invaluable Fields Editor. This tool lies very much at the heart of BCB database programming. To access it, you double-click or right-click on a `TTable` or `TQuery` object. The Fields Editor helps you associate custom objects with some or all of the fields from a table. By associating a custom object with a field, you can control the way a field displays, formats, validates, and inputs data. The Fields Editor also enables you to add new fields to a table at runtime and then to calculate the values that will be shown in the new fields. This latter procedure is referred to as calculated fields. Another benefit of the Fields Editor is that it helps you create lookup fields. Lookup fields perform a lookup from one table into another based on a foreign key. It's important to understand that the Fields Editor is just a utility. Everything done inside the Fields Editor could be done in code, although in this particular case the code in question becomes a little complicated, or at least time consuming. In this section and the next, you will be building a program called MASS, which illustrates both the Fields Editor and calculated fields. This is an important program, so you should try to use your copy of BCB to follow the steps described. ### Getting Started with the Fields Editor You can access the Fields Editor from either a `TTable` or `TQuery` object. To get started, drop a `TQuery` object on a data module, set up the `BCDEMOS` alias, enter the SQL statement `select * from animals`, and make the table active. (The `BCDEMOS` alias is created automatically when you install BCB. It points at the data in the `CBuilder/Examples/Data` directory. If you are having trouble with aliases, see the readme file on the CD or visit my Web site.) Drop down the Object Selector at the top of the Object Inspector. Notice that you currently have two components in use: `TDataModule` and `TQuery`. Right-click the `TQuery` object and select the Fields Editor menu choice to bring up the Fields Editor. Right-click the Fields Editor and select Add Fields from the menu to pop up the Add Fields dialog, as shown in Figure 11.1. FIGURE 11.1. The Add Fields dialog box from the Fields Editor. By default, all of the fields in the dialog box are selected. Click the OK button to select all five fields, and then close the Fields Editor. Open the Object Selector a second time; notice that there are now five new objects on your form, as shown in Figure 11.2. FIGURE 11.2. The Object Selector lists the objects created in the Fields Editor. You also can find this list in the `TForm1` class definition. These objects help you hone and define your presentation of the `Animals` table to the user. Here's a complete list of the objects you just created, as they appear in your header file:
    TStringField *AnimalsQueryNAME;

    TSmallintField *AnimalsQuerySIZE;

    TSmallintField *AnimalsQueryWEIGHT;

    TStringField *AnimalsQueryAREA;

    TBlobField *AnimalsQueryBMP;
The origins of the names shown here should be fairly obvious. The string `AnimalsQuery` comes from the name I've given to the `TQuery` object, and the second half of the name comes from the fields in the `Animals` table. This naming convention I've adopted here can be very useful if you are working with several tables and want to know at a glance which table and field are being referenced by a particular variable. NOTE: The names of the fields in the example shown here are capitalized only because the table in question is a dBASE table. dBASE tables automatically capitalize all letters in field names. If I had chosen to work with some other type of table, the capitalization of the letters in the field name would have followed the rules defined by the current database software. This principle appears again and again in BCB database programming. Whenever possible, the actual naming conventions and other traits of a server are surfaced inside of BCB. The developers of C++Builder did not aim to make all servers appear the same to you, but rather to give you access to their features as transparently as possible. Of course, generic tools such as the `TTable` object are also common in BCB. The VCL team, however, strove to find the right balance between expediting access to the database and unintentionally crippling the database by wrapping too many of its features inside objects. As a rule, the developers did whatever they could to ease the path, without ever going so far as to actually cut you off from a feature of the server you are accessing. This same principle applies to the VCL. The developers tried to do everything they could to make Windows programming simple, but they drew the line if a particular feature was so warm and fuzzy that it cut you off from direct access to the OS when and if you needed it. Each of the objects created in the Fields Editor is a descendant of `TField`. The exact type of descendant depends on the type of data in a particular field. For instance, the `AnimalsQueryWEIGHT` field is of type `TSmallIntField`, whereas the `AnimalsQueryNAME` field is of type `TStringField`. These are the two field types you will see most often. Other common types include `TDateField` and `TCurrencyField`, neither of which are used in this particular table. Remember that these types were selected to correspond with the field types in the table itself. `TStringField`, `TSmallIntField`, and the other objects shown here are all descendants of `TField` and share its traits. If you want, you can treat these objects exactly as you did the `TField` objects that you learned about in Chapter 9, "Using `TTable` and `TDataSet`." For instance, you can write this:
    S = MyIntegerField->AsString;
and this:
    S = MyIntegerField->Name;
However, these descendants of `TField` are very smart objects and have several traits that go beyond the functionality of their common ancestor. The most important property you will see is called `Value`. You can access it like this:
    void __fastcall TForm1::Button1Click(TObject *Sender)

    {

      int i;

      AnsiString S;

      DMod->AnimalsQuery->Edit();

      i = DMod->AnimalsQuerySIZE->Value;

      S = DMod->AnimalsQueryNAME->Value;

      i += 1;

      S = "Foo";

      DMod->AnimalsQuerySIZE->Value = i;

      DMod->AnimalsQueryNAME->Value = S;

    }
The code shown here first assigns values to the variables `i` and `S`. The next two lines change these values, and the last two lines reassign the new values to the objects. It usually wouldn't make much sense to write code exactly like this in a program, but it serves to illustrate the syntax used by `TField` descendants. (If you bother to try to write code like this as a test, remember that the `RequestLive` property for a `TQuery` object is set to `False` by default. You would have to set it to `True` before the code would work.) The `Value` property always conforms to the type of field you have instantiated. For instance, `TStringField`s are strings, whereas `TCurrencyField`s always return floating-point double values. However, if you show a `TCurrencyField` in a data-aware control, it will return a string that looks like this: `"$5.00"`. The dollar sign and the rounding to decimal places are simply part and parcel of what a `TCurrencyField` is all about. The preceding example might make you think that these variables are declared as `Variants`, which indeed is the case for the `TField` object itself. In the actual implementation, however, the `TCurrencyField->Value` is declared as a `Double`. If you tried to assign a string to it, you would get a type mismatch. Likewise, `TIntegerField.Value` is declared as a `LongInt`, and so on. `TSmallIntField` and `TWordField` are both descendants of `TIntegerField` and inherit the `Value` declaration as a `LongInt`. However, they have other internal code that affects the `Value` field, just as `TCurrencyField` rings some changes on its `Value` field to make it look like a monetary value. If you have the source, look up `DBTABLES.PAS` and `DB.PAS` to find the details of these constructions. (The Pascal source ships with some versions of BCB.) At any rate, the point here is that the preceding code is an example of polymorphism; it is not an example of relaxed type-checking. The `Value` field has a specific type--it's just that it undergoes polymorphic changes. If you want the names of each field in the current dataset, you should reference the `FieldName` property through one of the following two methods:
    S = AnimalsQuery->Fields[0]->FieldName;

    S = AnimalsQueryNAME.FieldName;
If you want the name of an object associated with a field, you should use the `Name` property:
    S = AnimalsQuery->Fields[0]->Name;

    S = AnimalsQueryNAME->Name;
When using the `ANIMALS` table, the first two examples shown previously yield the string `"Name"`, while the second two lines yield `"Query1NAME"`. Special properties are associated with most of the major field types. For instance, `TIntegerField`s have `DisplayFormat` and `DisplayEdit` properties, as well as `MinValue` and `MaxValue` properties. `TStringField`s, on the other hand, have none of these properties, but they do have an `EditMask` property, which works just like the `TEditMask` component found on the Additional page of the Component Palette. All these properties are used to control the way data is displayed to the user, or the way that input from the user should be handled. NOTE: I don't want to get ahead of myself, but properties such as `MinValue` and `MaxValue` are also used in the Data Dictionary, as will be explained later in this chapter. Changes made in the Data Dictionary will affect these values as seen in the Object Inspector, but changes in the Object Inspector will not affect the Data Dictionary. Don't worry if this doesn't make the slightest bit of sense yet, as I will get to the Data Dictionary in just a little while. ### Calculated Fields You should be aware of one more thing about the Fields Editor. You can use this tool not only to build objects that encapsulate existing fields, but also to build objects that represent new fields. For instance, suppose you wanted to create a sixth field, `Mass`, which contains the product of the `SIZE` and `WEIGHT` fields, in the `Animals` table. To create the `Mass` field, open the Fields Editor again, right-click it, and select the New Field menu choice. In the top part of the New Field dialog, enter the word `Mass`. Now set its type to `Integer`, and leave its field type as Calculated, as shown in Figure 11.3. FIGURE 11.3. Creating the `Mass` field in the Fields Editor. If you close the Fields Editor and add a `TDataSource` and `TDBGrid` to your project, you will see that the `Animals` table now appears to have six fields, the last of which is called `MASS`. Of course, it's one thing to create a field, and another to fill it in at runtime with an appropriate value. The act of placing a value in the new field you have created involves a concept called calculated fields. Calculated fields are one of the most valuable features of the `TField` object and its related architecture. You can use these calculated fields for several different purposes, but two stand out: If you need to perform calculations on two or more of the fields in a dataset and want to show the results of the calculations in a third field, you can use calculated fields. A scenario describing this type of situation was set up at the beginning of this section. If you are viewing one dataset and want to perform calculations or display data that involve lookups in at least one additional dataset, you can use the Fields Editor and calculated fields to show the results of these calculations in a new field of the first dataset. There is also a second, much better method for doing lookups. I will talk about that method later in this chapter. As a rule, you should do calculations in calculated fields, and lookups in lookup fields, though calculated fields are powerful enough to fill a number of different roles in your programs. The MASS program illustrates one example of the first of the two uses for calculated fields. You got this program started in the last section when you created the field called MASS and displayed it in a grid. To continue working with the MASS program, highlight the `AnimalsQuery` object and set the Object Inspector to the Events page. Now create an `OnCalcFields` event that looks like this:
    void __fastcall TDMod::AnimalsQueryCalcFields(TDataSet *DataSet)

    {

      AnimalsQueryMass->AsInteger =

        AnimalsQuerySIZE->AsInteger * AnimalsQueryWEIGHT->AsInteger;

    }
The code shown here assigns the value of the `AnimalsQueryMass` object to the product of the `AnimalsQuerySIZE` and `sqlWeightWEIGHT` fields. This kind of multiplication is legal to do because all of the fields are of the same type. Furthermore, you could have used the `Value` property instead of `AsInteger`. I explicitly declared the type in this example to help illustrate precisely what is going on. `OnCalcField` methods are called each time a record is displayed to the user. As a result, all of the `Mass` fields displayed in the grid are properly filled in, as shown in Figure 11.4. FIGURE 11.4. The `MASS` field contains the product of the `WEIGHT` and `SIZE` fields. A `TDBImage` control contains a bitmap from the `BMP` field of the table. To get the screen shot shown in Figure 11.4, I opened the `Column` property in the `TDBGrid` object and selected Add All Fields. I then deleted the `Area` and `BMP` fields and closed the `Column` property editor. I will talk more about the grid object later in this chapter. If you choose to never instantiate a particular field in the Fields Editor, the current dataset you are working with no longer contains that field. It can't be accessed programmatically or visually at runtime. Usually, this is exactly the effect you want to achieve, and so this trait will generally be perceived as a strong benefit. However, there are times when it might not serve your purposes, and in those cases you should either create an object for all the fields in a table or stay away from the Fields Editor altogether. Remember that you can hide fields inside a grid by using the `Column` property, as shown previously. That way, you create objects for all fields, but show only certain ones to the user. ### Lookup Fields You can use lookup fields to look up a value in one table that you want to use in a second table. For instance, suppose you had two tables, one of which contained a list of books, and the other contained a list of authors. It would be nice if you could automatically view a list of the existing authors whenever you needed to add a new book to the `Books` table. That way, you could enter the book's name, look up the author in a drop-down list, and presto, you would be done. The `Books` table would then automatically contain a reference to the appropriate author in the `Authors` table. That is, the author number from the `Authors` table would automatically be inserted in the `Books` table. Another way to think about lookup fields is that they provide the ability to perform a powerful kind of pseudo-join using the `TTable` object. Suppose two tables called `Authors` and `Books` are related on a field called `AuthNo`. `AuthNo` is the primary key of the `Authors` table, and it is a foreign key in the `Books` table. When you are looking at the `Books` table, sometimes you would like to be able to include the name of the author of each book inside the book table. That is, you would like to perform a join on the book and author table. You can't actually perform a join, however, because you are using the `TTable` object, and not `TQuery`. The solution to this dilemma is the lookup field. It will use the foreign key in the `Books` table to reference the name of the author from the `Author` table. This technique does join one better, however, because it will let you not only view a field from the `Authors` table as if it were part of the `Books` table, but also enables you to drop down a list of all the authors in the `Authors` table while you are still viewing the `Books` table, as shown in Figure 11.5. In short, lookup fields give you the same type of benefits you derive from performing a join between two tables. In particular, they let you combine the fields of two tables so that you can create one dataset with fields from multiple tables. NOTE: Lookup fields are a bit like a combination of a one-to-many relationship and a calculated field. The techniques used to actually implement them, however, have more in common with calculated fields than they do with one-to-many relationships. There are significant differences between the three technologies, but I still tend to think of calculated fields, lookup fields, and one-to-many relationships as being interrelated concepts. Because lookup fields are so much like one-to-many relationships, it is usually not a good idea to use both techniques simultaneously with the same two `TTable` objects. For instance, if you have the `Authors` table related to the books table in a one-to-many, you wouldn't want to simultaneously do a lookup from the `Books` table to the author table. This problem, and its solution, are addressed in the `Lookup` example on the CD-ROM that accompanies this book. That program will be discussed throughout the rest of this section of the chapter. I should perhaps add that lookup fields are a great technique to use with relatively small datasets. If you are from the world of big iron, and work with tables that contain tens of thousands of records or more, you will probably find lookup fields are of only limited use to you. Needless to say, BCB gives good support for using lookup fields. You can now perform automatic lookups inside grids, list boxes, and combo boxes. In particular, the following controls support lookups: `TDBGrid`, `TDBCtrlGrid`, `TDBLookupListBox`, and `TDBLookupComboBox`. The Lookup program shows how to proceed. The code for this application is shown in Listings 11.1 through 11.3. Two views of the program are shown in Figures 11.5 and 11.6. FIGURE 11.5. The main form for the Lookup program. ###### FIGURE 11.6. This form features a combo box that lets you perform lookups from the `Books` table into the `Authors` table. NOTE: The first version of Delphi had a `TDBLookupCombo` control and a `TDBLookupList` control that had certain limited capabilities. Both of these controls are still present in some versions of BCB, but they have been moved off the Data Controls page onto the Win 3.1 page. They are being kept around solely for compatibility with legacy Pascal code, and you should not use them in new programs. The `TDBLookupComboBox` control and the `TDBLookupListBox` control now replace the old 16-bit controls, and they outperform them on several fronts. In particular, the `TDBLookupComboBox` and `TDBLookupListBox` will be filled up automatically with the data from the lookup table. Don't confuse the old control with the new ones! `TDBLookupComboBox` is the fancy one; the `TDBLookupCombo` is the old-fashioned one. You might use the following somewhat whimsical mnemonic: The `TDBLookupListBox` has a bigger name than the `TDBLookupList` because it has "bigger" capabilities. By the way, this is a classic example of why it is important to get things right the first time. In particular, it shows why it is sometimes better to cut a feature rather than trying to put in a hack that you will want to improve in later versions. In particular, the `TDBLookupCombo` was poorly implemented in the first version of Delphi, which was a 16-bit program. Because Delphi 2.0 promised to compile all your 16-bit programs, this component had to be left in the product even though it was replaced with a far superior tool. Now, this old nemesis lives on even in the C++ version of the product, because BCB advertises the fact that it supports all the legacy Pascal code you might want to bring into a project. Here's the summary: The original error was made back in Delphi 1.0, but the repercussions still echo even when the 32-bit version of the Delphi is ported to C++! Clearly, it is worthwhile making sure that things are designed right the first time, or else they should be left out of the product altogether. Of course, this is a rule that can be stated fairly easily, but is difficult to live up to. Listing 11.1. The core functionality for the Lookup program is done in the Object Inspector for the TDMod object and not here in the code for Dmod1.cpp.
    ///////////////////////////////////////

    // File: DMod1.cpp

    // Project: Lookup

    // Copyright (c) 1997 by Charlie Calvert

    #include <vcl\vcl.h>

    #pragma hdrstop

    #include "DMod1.h"

    #pragma resource "*.dfm"

    TDMod *DMod;

    __fastcall TDMod::TDMod(TComponent* Owner)

      : TDataModule(Owner)

    {

      AuthorTable->Open();

      BookDetailTable->Open();

      BookLookupTable->Open();

    }

    void __fastcall TDMod::AuthorTableCalcFields(TDataSet *DataSet)

    {

      AuthorTableFirstLast->AsString =

        AuthorTableFirst->AsString + " " + AuthorTableLast->AsString;

    }

    void TDMod::RefreshBookDetail()

    {

      BookDetailTable->Refresh();

    }

    AnsiString TDMod::GetCurBook()

    {

      return BookDetailTable->FieldByName("Title")->AsString;

    }

    AnsiString TDMod::GetCurAuthor(void)

    {

      return AuthorTable->FieldByName("FirstLast")->AsString;

    }

    void TDMod::FindAuthor(AnsiString S)

    {

      AuthorTable->FindNearest(OPENARRAY(TVarRec, (S)));

    }

    void TDMod::FindTitle(AnsiString S)

    {

      AnsiString Temp(BookLookupTable->IndexName);

      BookLookupTable->IndexName = "idxTitle";

      BookLookupTable->FindNearest(OPENARRAY(TVarRec, (S)));

      BookLookupTable->IndexName = Temp;

    }

    void TDMod::BookLookupInsert()

    {

      BookLookupTable->Insert();

    }

    void TDMod::BookLookupPost()

    {

      if ((BookLookupTable->State == dsEdit)||(BookLookupTable->State == dsInsert))

        BookLookupTable->Post();

    }

    void TDMod::BookLookupCancel()

    {

      if ((BookLookupTable->State == dsEdit)||(BookLookupTable->State == dsInsert))

        BookLookupTable->Cancel();

    }

    void TDMod::BookLookupDelete()

    {

      BookLookupTable->Delete();

    }
Listing 11.2. Form1 gives you a look at both the Authors table and the Books table. A drop-down in dbGrid2 lets you view the lookup field.
    ///////////////////////////////////////

    // File: InsertEdit.cpp

    // Project: Lookup

    // Copyright (c) 1997 by Charlie Calvert

    #include <vcl\vcl.h>

    #pragma hdrstop

    #include "Main.h"

    #include "DMod1.h"

    #include "InsertEdit.h"

    #pragma resource "*.dfm"

    TForm1 *Form1;

    __fastcall TForm1::TForm1(TComponent* Owner)

      : TForm(Owner)

    {

    }

    void __fastcall TForm1::Exit1Click(TObject *Sender)

    {

      Close();

    }

    void __fastcall TForm1::EditBook1Click(TObject *Sender)

    {

      InsertEditForm->ShowEdit(DMod->CurBook);

      DMod->RefreshBookDetail();

    }

    void __fastcall TForm1::NewBook1Click(TObject *Sender)

    {

      InsertEditForm->ShowInsert();

      DMod->RefreshBookDetail();

    }
Listing 11.3. The InsertEditForm shows how to use DBLookupComboBoxes.
    ///////////////////////////////////////

    // File: InsertEdit.cpp

    // Project: Lookup

    // Copyright (c) 1997 by Charlie Calvert

    #include <vcl\vcl.h>

    #pragma hdrstop

    #include "InsertEdit.h"

    #include "DMod1.h"

    #pragma resource "*.dfm"

    TInsertEditForm *InsertEditForm;

    __fastcall TInsertEditForm::TInsertEditForm(TComponent* Owner)

      : TForm(Owner)

    {

    }

    void __fastcall TInsertEditForm::bbInsertClick(TObject *Sender)

    {

      DMod->BookLookupTable->Insert();

    }

    void __fastcall TInsertEditForm::bbPostClick(TObject *Sender)

    {

      DMod->BookLookupPost();

    }

    void TInsertEditForm::ShowEdit(AnsiString S)

    {

      DMod->FindTitle(S);

      ShowModal();

      DMod->BookLookupPost();

    }

    void TInsertEditForm::ShowInsert()

    {

      DMod->BookLookupInsert();

      DMod->BookLookupTableTitle->AsString = "My New Book";

      ShowModal();

      DMod->BookLookupPost();

    }

    void __fastcall TInsertEditForm::CancelBtnClick(TObject *Sender)

    {

      DMod->BookLookupCancel();

    }

    void __fastcall TInsertEditForm::DeleteBtnClick(TObject *Sender)

    {

      if (MessageBox(Handle, "Delete?" , "Delete Dialog", MB_YESNO) == ID_YES)

        DMod->BookLookupDelete();

    }

    void __fastcall TInsertEditForm::FormShow(TObject *Sender)

    {

      TitleEdit->SetFocus();

    }
The Lookup program enables you to easily fill in the key fields of the `Books` table by looking them up in the `Authors` table. To understand why this capability is important, notice that the only way to tell which author is associated with which book is by placing the appropriate author number in the `AuthNo` field of the `Book` table. This is convenient from the point of view of the programmer who wants to construct a well-made relational database. In particular, it saves space by allowing the construction of one-to-many relationships. However, the user isn't going to want to have to remember that Herman Melville is associated with the number 2, Jack Kerouac with the number x, and so on. The point of a lookup field is that it lets you look up a list of authors in the author table, and then automatically assigns the chosen author number to the `AuthNo` field in the `Books` table. This program uses two tables called, not surprisingly, `Author.db` and `Book.db`. Both of these tables are found on the CD-ROM that accompanies this book. Tables 11.1 and 11.2 show the schema for the tables. Table 11.1. Author.db table structure.
Name | Type | Keyed
`AuthNo` | `AutoInc` | Key
`First` | `Character(25)` | N/A
`Last` | `Character(25)` | N/A
`Dates` | `Character(25)` | N/A
`BirthPlace` | `Character(25)` | N/A
Table 11.2. Book.db table structure.
Name | Type | Keyed
`BookNo` | `AutoInc` | Key
`AuthNo` | `LongInt` | Foreign Key
`Title` | `Character (35)` | N/A
NOTE: Notice the use of the `AutoIncrement` fields in the table definitions shown in Tables 11.1 and 11.2. These fields will automatically be filled in when the user adds a new record at runtime. For instance, when you add the first record to the `Books` table, it will automatically be given a `BookNo` of `1`. The second record will automatically be given a `BookNo` of `2`, and so on. `AutoIncrement` fields are read-only, and frequently there is no need to show them to the user at runtime. Furthermore, I use Referential Integrity to ensure that the `AuthNo` field properly binds to the `Author` table. In particular, it ensures that you cannot insert records into the `Book` table that are not properly related to the `Authors` table through the `AuthNo` field. Referential Integrity also ensures that the value of the `AuthNo` field is filled in automatically when you insert a new record into the `Books` table. To view the Referential Integrity, load the book table into the Database Desktop, choose Table | Info Structure, select Referential Integrity in the Table Properties, highlight the `AuthNoRI` rule, and press the Detail Info button. There is more on this subject in the next chapter, called "Understanding Relational Databases." There is little actual work required to construct this program. In particular, look over the source code shown earlier, and you will see that the only significant line of code in the whole program is the one for the `OnCalcFields` event. Other than that, it's just a matter of manipulating the visual tools. To get started, create a new application and add a data module to it. Set up the `Authors` and `Books` tables on the data module. Bring up the Fields Editor for both tables and create objects for all of their fields. Give the tables and data sources appropriate names, such as `AuthorTable` and `BookLookupTable`, as shown in Figure 11.7. Note that later on I will add a second in- stance of the `Book` table to the program so that I can simultaneously perform a lookup and a one-to-many. FIGURE 11.7. The `TDataModule` for the Lookup program. Inside the `Author` table, create a calculated field called `LastFirst`. To create the calculated field, first right-click the `TTable` object, and then right-click the Fields Editor and select New from the menu. After creating the calculated field, assign the following method to the `OnCalcFields` event:
    void __fastcall TDMod::AuthorTableCalcFields(TDataSet *DataSet)

    {

      AuthorTableFirstLast->AsString =

        AuthorTableFirst->AsString + " " + AuthorTableLast->AsString;

    }
This field will be the one that is looked up in the second table. The issue here is that just looking up the last name of an author is not sufficient--you need to look up both first and last names in order to be sure you are finding a unique author. In other words, you can't tell Henry James from William James or Tom Wolfe from Thomas Wolfe unless you have both the first and last name present. It would be wasteful of disk space to permanently add a field to the table that combined the first and last names, but you can create a temporary copy of that field with a calculated field. Now that you have a calculated field in place, it is time to create a lookup field. To get started, bring up the Fields Editor for the `Book` table. Right-click it and create a new field called `AuthorLookup`. Set its `Type` to `String` and its `Field Type` to `Lookup`. The `KeyField` should be set to `AuthNo`, the `Dataset` to `AuthorTable`, the `Lookup Key` to `AuthNo`, and the `Result` field to `LastFirst`. Figure 11.8 shows how the New Field dialog should look when you are done. Notice that you can also fill in this same information in the Object Inspector if you first select the `BookLookupTable` object. (In other words, you could create a new object and then close the Fields Editor without specifying any of its properties. Later, you could select the object and designate its type, its lookup fields, and so on.) FIGURE 11.8. Filling in the New Field dialog. Go back to `Form1` and make sure the two `TDBGrids` are arranged one above the other and are hooked up properly to the tables on the `TDataModule`. Run the application. The `AuthorLookup` field in the `TDBGrid` object associated with the `Books` table is now a drop-down combo box. If you click it once, and then drop down its list, you can then perform a lookup into the `LastFirst` field of the `Author` table. This lookup will automatically fill in the `AuthNo` field of the book table. You can use this lookup to insert a new author into a new record or to change the author of an existing record. Note that lookup fields give you two distinct benefits. They enable you to perform a "join" between the `Books` table and the `Authors` table, and they allow you to look up a reference in a drop-down list. The implementation of this program found on the CD-ROM that accompanies this book actually enables the user to perform the lookup on a second form. I implement things that way because it is probably easiest from the user's perspective, and because I want to support both a one-to-many relationship and a lookup between the `Authors` and `Books` tables. However, if you just want to see how lookups work, then you should follow the technique described previously. NOTE: The capability of having a drop-down list in a grid object comes for free in BCB, even when you are not doing lookups. Go back in design mode and open up the `Columns` property of a grid object. Add all the fields to the `Columns` list box. You can now select one of the fields, such as `Title`, and choose the PickList button in order to create a set of default values available for the field. The user can access these values at runtime by clicking the field and dropping down the combo box, per the lookup example discussed previously. This is the capability supported by the old `TDBLookupList` and `TDBLookupCombo` from the old Windows 3.1 days. Besides the `TDBGrid` object, there are two other controls in BCB that understand lookup fields. The first of these controls is shown on `Form2` of the Lookup program found on the CD-ROM that accompanies this book. The `TDBLookupComboBox` is the default control you will get if you drag and drop the `AuthorLookup` field from the Fields Editor onto a form. If you perform the drag-and-drop operation, the control will be hooked up automatically. If you want to hook it up manually, just connect its `DataSource` to the `dsBook` object and its `DataField` to the `AuthorLookup` field. There is also a `TDBLookupListBox`, which works exactly the same way as the `TDBLookupComboBox`. NOTE: Both the `TDBLookupListBox` and `TDBLookupComboBox` have fields that correspond to the ones you filled in with the New Field dialog shown in Figure 11.7. However, there is no need to fill in these fields a second time. Just hook up the `DataSource` and `DataFields` properties, and you are ready to go. When you are working with the Lookup program found on the book's CD-ROM, you should note that `Form1` does not contain a lookup. It's meant to help you scan through all the available data so you can grok the significance of the lookup process. The top part of the second form, called the `InsertEditForm`, is somewhat closer to the type of display you would want to present to the user in a real program. However, I have extended this form to include a `TDBGrid` object, just so you can see how the lookup combo box is inserted automatically into the grid. When working with the `InsertEditForm`, notice how easy it is to simply type in a new book name, select an author in the combo box, and then perform a `Post` by clicking the OK button. The process is very simple from the user's point of view. In particular, a new `BookNo` is being assigned automatically by the `AutoIncrement` field, and the new `AuthNo` is being filled in automatically by the lookup process. Here are the two ways to handle the data in `InsertEditForm`: `InsertMode`: A temporary book name appears in the `TitleEdit` control when you bring up the form in this mode. Type in the name of the book you want to record in the `Title` field, and then drop down the `AuthorLookup` combo to select an author. `EditMode`: There is usually data in both the `Title` and `AuthorLookup` fields when you bring up the mode in this form. The user can either edit the `Title` field, or associate the book with a new author. I call a method called `FindTitle` in the `DMod` module to make sure that the form shows the record the user wants to edit. It's important to note that lookup controls probably would not be appropriate for use with big datasets because drop-down controls aren't very handy for displaying thousands of items. Even list boxes are fairly limited in these circumstances. You would therefore use lookups mostly with smaller datasets. It should also be pointed out that not being able to use both one-to-many relationships and lookups between the same two tables is a significant inconvenience. However, the Lookup example discussed in these sections and implemented on the book's CD-ROM shows that the workaround is not that complex. NOTE: If you are concerned that the solution to the problem in the last paragraph requires using three `TTable` objects instead of only two, I would ask you to recall that the goal of this book is to show how to get things done, not how to do things in the smallest possible space. If you can get things done in five minutes through a technique that you know is bug free, that is something you should give up only reluctantly, particularly if the alternative is working for days or weeks to implement a solution that is likely to have bugs that will take another week or two to squash. At the very least, you should implement the quick solution for the first draft of your application, and then come back and look for optimizations once you have a working version of the product. Don't ever try to optimize during your first draft of an application! There is no such thing as getting things right the first time in programming. Instead, you should implement a reasonable solution, critique it, come back and make improvements, critique the improvements, make another pass over the application, and so on. This kind of cycle demands that you not get too hung up on optimizations during early drafts, because you are likely to find that any one part of the application will change in future revisions. If you have the thing implemented correctly, and there is still time left in the project cycle, then you can come back and seek to optimize the code! The final kicker in this analysis is that contemporary application programmers rarely have time to optimize. Given the success rate of most projects, your customers or managers will usually be ecstatic if you just turn in a working solution to the problem on time. If you release the same application 10 percent faster and 20 percent smaller, but six months later, it's unlikely you will win quite the same number of kudos you think you deserve. I usually leave the minute optimizations up to the development teams at Borland. They know how to reach into the fire without getting burned. If you want to change the author associated with a particular record, you just click a new item in the list box. The author number will be changed automatically for you by the lookup. It's all very simple and intuitive when viewed from the user's perspective. ### TDBGrid at Runtime `TDBGrid` objects can be completely reconfigured at runtime. You can hide and show columns, change the order of columns, the color of columns, the color of rows, the color of fields, and the width of columns. The GridTricks program, shown in Figure 11.9, demonstrates how to take a `TDBGrid` through its paces at runtime. The program is fairly straightforward except for two brief passages. The first passage involves creating checkbox controls on-the-fly, and the second shows how to change the traits of columns. FIGURE 11.9. The main GridTricks program enables you to change the appearance of a grid at runtime. You need a color monitor to really see what is happening. When the user wants to decide which fields are visible, GridTricks pops up a second form and displays the names of all the fields from the `ORDERS` table in a series of checkboxes. The user can then select the fields that he or she wants to make visible. The selected checkboxes designate fields that are visible, whereas the nonselected ones represent invisible fields. The program also enables you to set the order and width of fields, as well as to hide and show the titles at the top of the grid. (See Listings 11.1 and 11.2.) The code for the GridTricks program is in the `CHAP17` directory on this book's CD-ROM. (See Listings 11.4-11.8.) Listing 11.4. The main unit for the GridTricks Program.
    ///////////////////////////////////////

    // File: Main.cpp

    // Project: GridTricks

    // Copyright (c) 1997 by Charlie Calvert

    #include <vcl\vcl.h>

    #pragma hdrstop

    #include "Main.h"

    #include "DMod1.h"

    #include "NamesDlg.h"

    #include "ColumnEditor1.h"

    #include "ShowOptions1.h"

    #define NEWCOLOR clGreen

    #pragma resource "*.dfm"

    TForm1 *Form1;

    __fastcall TForm1::TForm1(TComponent* Owner)

      : TForm(Owner)

    {

    }

    void __fastcall TForm1::Exit1Click(TObject *Sender)

    {

      Close();

    }

    void __fastcall TForm1::FieldNames1Click(TObject *Sender)

    {

      NamesDialog->ShowNames(fdFieldNames);

    }

    void __fastcall TForm1::FieldObjectNames1Click(TObject *Sender)

    {

      NamesDialog->ShowNames(fdObjectNames);

    }

    ///////////////////////////////////////

    // DBGrid1DrawColumnCell

    // Paints ROW different color depending on value of ItemsTotal field

    ///////////////////////////////////////

    void __fastcall TForm1::DBGrid1DrawColumnCell(TObject *Sender,

          const TRect &Rect, Integer DataCol, TColumn *Column,

          TGridDrawState State)

    {

       if (ColorRows1->Checked)

       {

         if (DMod->OrdersTableItemsTotal->Value < 1000)

           DBGrid1->Canvas->Font->Color = clRed;

         else if (DMod->OrdersTableItemsTotal->Value < 10000)

           DBGrid1->Canvas->Font->Color = clBlue;

         else

           DBGrid1->Canvas->Font->Color = clGreen;

       }

       DBGrid1->DefaultDrawColumnCell(Rect, DataCol, Column, State);

    }

    void TForm1::ColorTitles(BOOL UseDefaultColor)

    {

      TColor Colors[] = {clRed, clBlue, clGreen, clLime, clWhite, clFuchsia};

      int i;

      for (i = 0; i < DBGrid1->Columns->Count; i++)

      {

        TColumn *Column = DBGrid1->Columns->Items[i];

        TColumnTitle *ColumnTitle = Column->Title;

        if (UseDefaultColor)

          ColumnTitle->Font->Color = FDefaultColor;

        else

          ColumnTitle->Font->Color = Colors[random(7)];

      }

    }

    void __fastcall TForm1::AnimateTitles1Click(TObject *Sender)

    {

      Timer1->Enabled = (!Timer1->Enabled);

      AnimateTitles1->Checked = Timer1->Enabled;

      if (!AnimateTitles1->Checked)

        ColorTitles(True);

    }

    void __fastcall TForm1::ColorRows1Click(TObject *Sender)

    {

      ColorRows1->Checked = (!ColorRows1->Checked);

      DBGrid1->Repaint();

    }

    void __fastcall TForm1::MarkColumnClick(TObject *Sender)

    {

      MarkColumn->Checked = (!MarkColumn->Checked);

      TColumn *Column = DBGrid1->Columns->Items[DBGrid1->SelectedIndex];

      if (MarkColumn->Checked)

      {

        Column->Font->Color = NEWCOLOR;

        Column->Font->Style = TFontStyles() << fsBold;

      }

      else

      {

        Column->Font->Color = FDefaultColor;

        Column->Font->Style = TFontStyles();

      }

      HandleCaption();

    }

    ///////////////////////////////////////

    // Handle Caption

    ///////////////////////////////////////

    void TForm1::HandleCaption()

    {

      TColumn *Column = DBGrid1->Columns->Items[DBGrid1->SelectedIndex];

      AnsiString S(DBGrid1->SelectedIndex);

      Caption = S;

      if (Column->Font->Color == FDefaultColor)

        Caption = "Column " + S + " is Default";

      else

        Caption = "Column " + S + " is Marked";

    }

    void __fastcall TForm1::DBGrid1ColEnter(TObject *Sender)

    {

      TColumn *Column = DBGrid1->Columns->Items[DBGrid1->SelectedIndex];

      MarkColumn->Checked = (Column->Font->Color == NEWCOLOR);

      HandleCaption();

    }

    void __fastcall TForm1::FormCreate(TObject *Sender)

    {

      FDefaultColor = DBGrid1->Font->Color;

      HandleCaption();

    }

    void __fastcall TForm1::Timer1Timer(TObject *Sender)

    {

      ColorTitles(False);

    }

    void __fastcall TForm1::ShowFieldEditor1Click(TObject *Sender)

    {

      ColumnEditor->ShowColumns();

    }

    void __fastcall TForm1::ToggleTitles1Click(TObject *Sender)

    {

      if (DBGrid1->Options.Contains(dgTitles))

        DBGrid1->Options = TDBGridOptions(DBGrid1->Options) >> dgTitles;

      else

        DBGrid1->Options = TDBGridOptions(DBGrid1->Options) << dgTitles;

    }

    void __fastcall TForm1::ToggleIndicator1Click(TObject *Sender)

    {

      if (DBGrid1->Options.Contains(dgIndicator))

        DBGrid1->Options = TDBGridOptions(DBGrid1->Options) >> dgIndicator;

      else

        DBGrid1->Options = TDBGridOptions(DBGrid1->Options) << dgIndicator;

    }

    void __fastcall TForm1::ShowTitlesIndicator1Click(TObject *Sender)

    {

      ShowTitlesIndicator1->Checked =

        !(TDBGridOptions(DBGrid1->Options).Contains(dgIndicator) &&

        TDBGridOptions(DBGrid1->Options).Contains(dgTitles));

      if (ShowTitlesIndicator1->Checked)

        DBGrid1->Options = TDBGridOptions(DBGrid1->Options) << dgIndicator << dgTitles;

      else

        DBGrid1->Options = TDBGridOptions(DBGrid1->Options) >> dgIndicator >> dgTitles;

      DBGrid1->Refresh();

    }

    void __fastcall TForm1::ColLines1Click(TObject *Sender)

    {

      if (DBGrid1->Options.Contains(dgColLines))

        DBGrid1->Options = TDBGridOptions(DBGrid1->Options) >> dgColLines;

      else

        DBGrid1->Options = TDBGridOptions(DBGrid1->Options) << dgColLines;

      ColLines1->Checked = DBGrid1->Options.Contains(dgColLines);

    }

    void __fastcall TForm1::RowLines1Click(TObject *Sender)

    {

      if (DBGrid1->Options.Contains(dgRowLines))

        DBGrid1->Options = TDBGridOptions(DBGrid1->Options) >> dgRowLines;

      else

        DBGrid1->Options = TDBGridOptions(DBGrid1->Options) << dgRowLines;

      RowLines1->Checked = DBGrid1->Options.Contains(dgRowLines);

    }

    void __fastcall TForm1::ShowAllOptions1Click(TObject *Sender)

    {

      ShowOptionsForm->ShowOptions(DBGrid1->Options);

    }

    void __fastcall TForm1::MustPressF2orEntertoEdit1Click(TObject *Sender)

    {

      DBGrid1->Options = TDBGridOptions(DBGrid1->Options) >> dgAlwaysShowEditor;

      MustPressF2orEntertoEdit1->Checked =

        !TDBGridOptions(DBGrid1->Options).Contains(dgAlwaysShowEditor);

    }

    void __fastcall TForm1::ChangeWidthofField1Click(TObject *Sender)

    {

      AnsiString S("");

      TColumn *Column = DBGrid1->Columns->Items[DBGrid1->SelectedIndex];

      if (InputQuery("Data Needed", "New Width of Selected Field", S))

        Column->Width = S.ToInt();

    }

    void __fastcall TForm1::HideCurrentColumn1Click(TObject *Sender)

    {

      if (MessageBox(Handle, "Hide Column?",

           "Hide Info?", MB_YESNO | MB_ICONQUESTION) == ID_YES)

      {

        TColumn *Column = DBGrid1->Columns->Items[DBGrid1->SelectedIndex];

        Column->Free();

      }

    }

    void __fastcall TForm1::MoveCurrentColumn1Click(TObject *Sender)

    {

      AnsiString S("");

      if (InputQuery("Data Needed", "Enter new position of column", S))

      {

        DMod->OrdersTable->Fields[DBGrid1->SelectedIndex]->Index = S.ToInt();

      }

    }
Listing 11.5. The data module for the GridTricks program.
    ///////////////////////////////////////

    // File: ColumnEditor1.cpp

    // Project: GridTricks

    // Copyright (c) 1997 by Charlie Calvert

    #include <vcl\vcl.h>

    #pragma hdrstop

    #include "DMod1.h"

    #pragma resource "*.dfm"

    TDMod *DMod;

    __fastcall TDMod::TDMod(TComponent* Owner)

      : TDataModule(Owner)

    {

    }

    void TDMod::GetFieldNames(TStringList *Items)

    {

      int i;

      Items->Clear();

      for (i = 0; i < OrdersTable->FieldCount - 1; i++)

        Items->Add(OrdersTable->Fields[i]->FieldName);

    }

    void TDMod::GetObjectNames(TStringList *Items)

    {

      int i;

      Items->Clear();

      for (i = 0; i < OrdersTable->FieldCount - 1; i++)

        Items->Add(OrdersTable->Fields[i]->Name);

    }
Listing 11.6. The Column Editor for the GridTricks program.
    ///////////////////////////////////////

    // File: ColumnEditor1.cpp

    // Project: GridTricks

    // Copyright (c) 1997 by Charlie Calvert

    // The last build of BCB I checked this on before shipping

    // was still broken. BCB was not properly updating the

    // data on the grids. Hopefully this program will start

    // working properly once there is an update for BCB 1.0.

    // The code I have here works fine in Delphi. In short, the

    // problem is not in my code, and its not in the VCL. Its a

    // BCB problem.

    //

    // Check my website for updates: users.aol.com/charliecal

    //

    #include <vcl\vcl.h>

    #pragma hdrstop

    #include "ColumnEditor1.h"

    #include "DMod1.h"

    #include "Main.h"

    #pragma resource "*.dfm"

    #define GAP 2

    TColumnEditor *ColumnEditor;

    //--------------------------------------------------------------------------

    __fastcall TColumnEditor::TColumnEditor(TComponent* Owner)

      : TForm(Owner)

    {

    }

    void TColumnEditor::CreateCheckBox(int Index, AnsiString Name, BOOL Visible)

    {

      CheckBoxAry[Index] = (TCheckBox*) new TCustomCheckBox(this);

      CheckBoxAry[Index]->Parent = ColumnEditor;

      CheckBoxAry[Index]->Caption = Name;

      CheckBoxAry[Index]->Left = 10;

      CheckBoxAry[Index]->Top = Index * (CheckBoxAry[Index]->Height + GAP);

      CheckBoxAry[Index]->Width = 200;

      CheckBoxAry[Index]->Checked = Visible;

    }

    void TColumnEditor::ShowColumns(void)

    {

      int i;

      TColumn *Column;

      for (i = 0; i < DMod->OrdersTable->FieldCount; i++)

        CreateCheckBox(i, DMod->OrdersTable->Fields[i]->Name,

          DMod->OrdersTable->Fields[i]->Visible);

      Height = (DMod->OrdersTable->FieldCount - 1) * (CheckBoxAry[0]->Height + GAP);

      if (Height > 470)

        Height = 470;

      ShowModal();

      for (i = 0; i < DMod->OrdersTable->FieldCount; i++)

        DMod->OrdersTable->Fields[i]->Visible = CheckBoxAry[i]->Checked;

    }
Listing 11.7. The NamesDlg for the GridTricks program.
    ///////////////////////////////////////

    // File: NamesDlg.cpp

    // Project: GridTricks

    // Copyright (c) 1997 by Charlie Calvert

    #include <vcl\vcl.h>

    #pragma hdrstop

    #include "NamesDlg.h"

    #include "DMod1.h"

    #pragma resource "*.dfm"

    TNamesDialog *NamesDialog;

    __fastcall TNamesDialog::TNamesDialog(TComponent* Owner)

      : TForm(Owner)

    {

    }

    void TNamesDialog::ShowNames(TFieldData FieldData)

    {

      switch(FieldData)

      {

        case fdFieldNames:

          DMod->GetFieldNames((TStringList *)ListBox1->Items);

          break;

        case fdObjectNames:

          DMod->GetObjectNames((TStringList *)ListBox1->Items);

          break;

      }

      Show();

    }

    void __fastcall TNamesDialog::BitBtn1Click(TObject *Sender)

    {

      Close();

    }
Listing 11.8. The ShowOptions module shows which DBGrid options are currently active.
    #include <vcl\vcl.h>

    #pragma hdrstop

    #include "ShowOptions1.h"

    #pragma resource "*.dfm"

    TShowOptionsForm *ShowOptionsForm;

    __fastcall TShowOptionsForm::TShowOptionsForm(TComponent* Owner)

      : TForm(Owner)

    {

      int i;

      int j = 0;

      for (i = 0; i < ComponentCount; i++)

        if (dynamic_cast<TCheckBox*>(Components[i]))

        {

          CheckBox[j] = (TCheckBox*)Components[i];

          j++;

        }

    }

    void TShowOptionsForm::ShowOptions(TDBGridOptions Options)

    {

      int i;

      for (i = 0; i < 12; i++)

        if (Options.Contains(i))

          CheckBox[i]->Checked = True;

        else

          CheckBox[i]->Checked = False;

      ShowModal();

    }
In the next few paragraphs you will find descriptions of the key parts of the GridTricks program. Understanding its constituent parts will help you to take control over the grids you display in your programs. Most of the code in the GridTricks program is fairly simple. However, the program performs a number of separate tasks. To grasp the program, it's necessary to divide and conquer; that is, to take the tasks performed by the program one at a time. Find out how each one works, and then move on to the next one. If you proceed in this fashion, you will find the program easy to understand. #### Controlling the Options Property of a DBGrid at Runtime You can use the `Options` field of a `TDBGrid` to change its appearance. The `Options` property has the following possible values:
`dgEditing` | Set to `True` by default, it enables the user to edit a grid. You can also set the grid's `ReadOnly` property to `True` or `False`.
`dgTitles` | Designates whether titles can be seen.
`dgIndicator` | Determines whether to show the small icons on the left of the grid.
`dgColumnResize` | Designates whether or not the user can resize columns.
`dgColLines` | Determines whether or not to show the lines between columns.
`dgRowLines` | Designates whether or not to show the lines between rows.
`dgTabs` | Enables the user to tab and Shift+tab between columns.
`dgAlwaysShowEditor` | If you select a field will you be in Edit mode auto-matically?
`dgRowSelect` | Can select rows, mutually exclusive with `dgAlwaysShowEditor`.
`dgAlwaysShowSelection` | Selection remains even when grid loses focus.
`dgConfirmDelete` | Shows message box when user presses Ctrl+Delete.
`dgCancelOnExit` | Cancels Inserts on exit if no changes were made to row.
`dgMultiSelect` | Can select multiple noncontiguous rows with Ctrl+Click.
Here is the declaration for the enumerated type where these values are declared:
    enum TDBGridOption { dgEditing, dgAlwaysShowEditor, dgTitles, dgIndicator,

                         dgColumnResize, dgColLines, dgRowLines, dgTabs, dgRowSelect,

                         dgAlwaysShowSelection, dgConfirmDelete, dgCancelOnExit,  dgMultiSelect };
For instance, you can set the options at runtime by writing code that looks like this:
    DBGrid1->Options = TDBGridOptions() << dgTitles;
This code in effect turns all the options to `False` except `dgTitles`. This code turns off all options but `dgTitles` and `dgIndicator`.
    DBGrid1->Options = TDBGridOptions() << dgTitles << dgIndicator;
More specifically, the code sets the `DBGrid1` `Options` property to a set that contains only `dgTitles` and `dgIndicator`. This code toggles `dgTitles` and `dgIndicator` off and on each time it is called:
    void __fastcall TForm1::ToggleTitles1Click(TObject *Sender)

    {

      if (DBGrid1->Options.Contains(dgTitles))

        DBGrid1->Options = TDBGridOptions(DBGrid1->Options) >> dgTitles;

      else

        DBGrid1->Options = TDBGridOptions(DBGrid1->Options) << dgTitles;

    }
The set operators shown in `ToggleTitles1Click` move the `dgTitles` option in and out of `DBGrid->Options`. The following code shows how to toggle back and forth between showing both indicators and titles and hiding both indicators and titles:
    void __fastcall TForm1::ShowTitlesIndicator1Click(TObject *Sender)

    {

      ShowTitlesIndicator1->Checked =

        !(TDBGridOptions(DBGrid1->Options).Contains(dgIndicator) &&

        TDBGridOptions(DBGrid1->Options).Contains(dgTitles));

      if (ShowTitlesIndicator1->Checked)

        DBGrid1->Options = TDBGridOptions(DBGrid1->Options) << dgIndicator << dgTitles;

      else

        DBGrid1->Options = TDBGridOptions(DBGrid1->Options) >> dgIndicator >> dgTitles;

      DBGrid1->Refresh();

    }
> This code moves both the `dgIndicator` and `dgTitles` elements in and out of the `Options` array as needed. The `<<` operator adds elements to a set, while the `>>` operator moves things out of the set. If you need to move multiple elements in and out of the set, just use the operator multiple times as shown in the `ShowTitlesIndicator1Click` method. NOTE: The following standard set operations will not work with the `Options` property because the `+=` and `-=` operators do not work with sets that are properties because of the extra work involved in calling get and set methods:
>     TDBGridOptions Options;

>     Options << dgTitles << dgIndicator;

>     DBGrid1->Options += Options;

>     DBGrid1->Options -= Options;
Here is an example of code showing how to toggle the `dgRowLines` element of the `Options` property on and off at runtime.
    void __fastcall TForm1::RowLines1Click(TObject *Sender)

    {

      if (DBGrid1->Options.Contains(dgRowLines))

        DBGrid1->Options = TDBGridOptions(DBGrid1->Options) >> dgRowLines;

      else

        DBGrid1->Options = TDBGridOptions(DBGrid1->Options) << dgRowLines;

      RowLines1->Checked = DBGrid1->Options.Contains(dgRowLines);

    }
The last line of code in the routine toggles the check mark before the `RowLines` menu item so that it reflects the current state of the grid. In other words, if `dgRowLines` is part of the set, the menu item will be checked; if it is not part of the set, the menu item will not be checked:
    RowLines1->Checked = DBGrid1->Options.Contains(dgRowLines);
In this section you have seen how to toggle the elements of the `Options` set back and forth at runtime. Most of the code for doing this is fairly simple, although you need to have a basic grasp of BCB set operations to understand how it works. If you need to brush up on this material, sets were covered in more depth in Chapter 2, "Basic Facts About C++Builder." #### Displaying the DBGrid Options at Runtime Now that you know how to toggle the `Options` of a `DBGrid`, it might be worthwhile spending a few moments learning how to display the `Options` to the user at runtime. As shown in Figure 11.10, I use a set of 12 `CheckBox`es to depict the current state of 12 `DBGrid` options. In the next few paragraphs I will explain how the code that drives this form works. FIGURE 11.10. Using checkboxes to depict the available `DBGrid` options to the user at runtime. In the header file for the unit, I declare an array of checkboxes:
    TCheckBox *CheckBox[12];
I initialize these checkboxes in the constructor for the form:
    __fastcall TShowOptionsForm::TShowOptionsForm(TComponent* Owner)

      : TForm(Owner)

    {

      int i;

      int j = 0;

      for (i = 0; i < ComponentCount; i++)

        if (dynamic_cast<TCheckBox*>(Components[i]))

        {

          CheckBox[j] = (TCheckBox*)Components[i];

          j++;

        }

    }
This code iterates over all the components on the form checking for ones that are of type `TCheckBox`. When it finds one, it adds it to the array of `CheckBox`es. The code uses `dynamic_cast` to check whether each item in the `Components` array is of type `TCheckBox`. NOTE: A `Components` array is implemented in `TComponent` and is maintained automatically for all components that descend from `TComponent`. The concept of ownership is what governs which items are put in the `Components` array. All components that are owned by the form are automatically, and by definition, included in the `Components` array for the form. In other words, if you drop a component on a form, it will be listed in the `Components` array for the form. You can use the `ComponentCount` property of the form to determine how many items are in the `Components` array. After filling in the array of checkboxes, it is a simple matter to toggle the `Checked` property of each checkbox depending on the current state of each `DBGrid` option:
    void TShowOptionsForm::ShowOptions(TDBGridOptions Options)

    {

      int i;

      for (i = 0; i < 12; i++)

        if (Options.Contains(TDBGridOption(i)))

          CheckBox[i]->Checked = True;

        else

          CheckBox[i]->Checked = False;

      ShowModal();

    }
This code determines which items in the `DBGridOptions` set are turned on, and then toggles the appropriate checkbox. The code depends, of course, on the fact that the `DBGridOptions` set is a list of items with values ranging from 0 to 11. To understand this code, you must grasp that `DBGridOption` is an enumerated type, and `DBGridOptions` is a set ranging over the values in that enumerated type, with `dgEditing` being the minimum value and `dgMultiSelect` being the maximum value:
    enum TDBGridOption {dgEditing, dgAlwaysShowEditor, dgTitles, dgIndicator,

                        dgColumnResize, dgColLines, dgRowLines, dgTabs, dgRowSelect,

                        dgAlwaysShowSelection, dgConfirmDelete, dgCancelOnExit,

                        dgMultiSelect};

    typedef Set<TDBGridOption, dgEditing, dgMultiSelect>  TDBGridOptions;
### Changing the Colors and Fonts in a Grid The next three sections of the chapter cover changing the colors of all the titles, columns, rows, and even individual cells in a `TDBGrid`. This is not something you have to do all that often, but when the need comes around it is fairly pressing. Before reading these sections, you should be sure to run the GridTricks program, because it will be hard to read the code without some understanding of what it does. #### Changing the Titles in a TDBGrid Object Here is how to color the titles in a `TDBGrid`:
    void TForm1::ColorTitles(BOOL UseDefaultColor)

    {

      TColor Colors[] = {clRed, clBlue, clGreen, clLime, clWhite, clFuchsia};

      int i;

      for (i = 0; i < DBGrid1->Columns->Count; i++)

      {

        TColumn *Column = DBGrid1->Columns->Items[i];

        TColumnTitle *ColumnTitle = Column->Title;

        if (UseDefaultColor)

          ColumnTitle->Font->Color = FDefaultColor;

        else

          ColumnTitle->Font->Color = Colors[random(7)];

      }

    }
This code first declares an array of colors. The constants seen here are pre-declared colors of type `TColor`. The actual number of colors in the array was chosen at random. I could have added or subtracted colors from the array without changing the rest of code in the routine, with the exception of the number 7, which is passed to random in the routine's last line of code. The `TColumn` object defines how a column in a `TDBGrid` should look. That is, it defines the font, color, and width of the column. The `Columns` property of a `TDBGrid` is of type `TDBGridColumns`, which is a collection of `TColumn` objects. Each `TColumn` object has a title. This title is defined in an object of type `TColumnTitle`. Finally, a `TColumnTitle` has color, font, and caption properties:
    TDBGrid Object

      Columns Property

        TColumn Object

          TColumnTitle

            Font, Color, Caption
The preceding list is not an object hierarchy, but just a way of illustrating the relationship between these different entities. In other words, the grid object contains a `Columns` property, and the `Columns` property contains `TColumn` objects, and the `TColumn` object contains a `TColumnTitle`, which in turn contains a `Font`, `Color`, and `Caption`. To get hold of a `TColumn` object, you can use the `Items` property of `TDBGridColumns`:
    TColumn *Column = DBGrid1->Columns->Items[i];
To move from there to a `TColumnTitle` object, you can use the `Title` property of a `TColumn` object:
    TColumnTitle *ColumnTitle = Column->Title;
Once the preceding `ColorTitle`s method has the `ColumnTitle` in its hands, it can set it to whatever color it wants:
    if (UseDefaultColor)

      ColumnTitle->Font->Color = FDefaultColor;

    else

      ColumnTitle->Font->Color = Colors[random(7)];
The `FDefaultColor` variable is of type `TColor`. In the `OnCreate` event for the form, I set it to the default color for the grid's font:
    FDefaultColor = DBGrid1->Font->Color;
Phew! That was the hard one. If you understand what has happened here, you will have no trouble with the next two sections, which cover changing the color of columns and rows in a grid. #### Changing an Entire Column in a Grid If you understand the code in the last section, it will be easy to understand how to change the look of a single column. Writing this kind of code will enable you to emphasize a certain part of a dataset or to bring the users eye to certain part of your form. Here is the method that changes the appearance of a column in a `TDBGrid`:
    void __fastcall TForm1::MarkColumnClick(TObject *Sender)

    {

      MarkColumn->Checked = (!MarkColumn->Checked);

      TColumn *Column = DBGrid1->Columns->Items[DBGrid1->SelectedIndex];

      if (MarkColumn->Checked)

      {

        Column->Font->Color = NEWCOLOR;

        Column->Font->Style = TFontStyles() << fsBold;

      }

      else

      {

        Column->Font->Color = FDefaultColor;

        Column->Font->Style = TFontStyles();

      }

      HandleCaption();

    }
This code first grabs hold of a selected column in a grid:
    TColumn *Column = DBGrid1->Columns->Items[DBGrid1->SelectedIndex];
If the user has indicated that he wants this column to stand out, it is a simple matter to change its color and set its font to bold:
    Column->Font->Color = NEWCOLOR;

    Column->Font->Style = TFontStyles() << fsBold;
Notice that the `Style` property is a set, and so you use a template class to manipulate its members. Here, as found in `GRAPHICS.HPP`, are the different styles you can associate with a font:
    enum TFontStyle { fsBold, fsItalic, fsUnderline, fsStrikeOut };
There are some parts of the code, such as `MarkColumn` and `HandleCaption`, that I don't mention. I ignore these elements because they are merely part of the logic of this program and are not germane to the subject of changing an individual column. #### Changing the Color of a Row in a Grid In the last two sections on columns and column titles, you have been working with the `TColumn` object. You can also change the color of the text in a `TDBGrid` by working with the font associated with the grid's `TCanvas` object:
    void __fastcall TForm1::DBGrid1DrawColumnCell(TObject *Sender,

          const TRect &Rect, Integer DataCol, TColumn *Column,

          TGridDrawState State)

    {

       if (ColorRows1->Checked)

       {

         if (DMod->tblOrdersItemsTotal->Value < 1000)

           DBGrid1->Canvas->Font->Color = clRed;

         else if (DMod->tblOrdersItemsTotal->Value < 10000)

           DBGrid1->Canvas->Font->Color = clBlue;

         else

           DBGrid1->Canvas->Font->Color = clGreen;

       }

       DBGrid1->DefaultDrawColumnCell(Rect, DataCol, Column, State);

    }
If you run the GridTricks program, you can see the effect of this code by choosing Color Rows from the Options menu of that program. Be sure that none of the other special effects are turned on when you choose this option, because they can interfere with your ability to see its results. Be sure to scroll the grid up and down after turning the effect on, because the data at the top of the grid is fairly homogenous. The data shown in the grid is from the `Orders` table in the `BCDEMOS` database. The code shown here colors each row in the grid according to the amount of money reported in the `ItemsTotal` field of the `Orders` table. For instance, if the `ItemsTotal` field contains a sum less than $1,000 dollars, that row is painted `Red`:
    DBGrid1->Canvas->Font->Color = clRed;
Here the code sets the font of the `TCanvas` object for the grid to `clRed`. Nothing could be simpler. #### Changing the Width of a Column The user can change the width of a column at runtime with the mouse. But how can you do the same thing programmatically without any input from the user? If you want to change the width of a column at runtime, just change the `DisplayWidth` property of the appropriate `TField` object:
    TblOrders->FieldByName("CustNo")->DisplayWidth = 12;

    TblOrdersCustNo->DisplayWidth = 12;
The value `12` refers to the approximate number of characters that can be displayed in the control. Various factors, such as whether or not you are using a fixed-pitch font, affect the interpretation of this value. See the online help for additional information. Here is how you can change the width of the column in the grid without affecting the properties of the underlying field:
    void __fastcall TForm1::ChangeWidthofField1Click(TObject *Sender)

    {

      AnsiString S("");

      TColumn *Column = DBGrid1->Columns->Items[DBGrid1->SelectedIndex];

      if (InputQuery("Data Needed", "New Width of Selected Field", S))

        Column->Width = S.ToInt();

    }
This code asks the user for the width he or she wants to assign to the currently selected column. The code then makes the change by retrieving the column and changing its `Width` property. #### Hiding or Moving Columns in a TDBGrid The user can change the order of columns in a `TDBGrid` simply by clicking them and dragging them with a mouse. But how do you proceed if you want to do the same thing at runtime without the user's direct input? If you want to hide a field at runtime, you can set its `Visible` property to `False`:
    OrdersTable->FieldByName("CustNo")->Visible = False;

    OrdersTableCustNo->Visible = False;
NOTE: This code will not work in the first version of BCB. See the top of the `ColumnEditor1.cpp` file for late-breaking information on this process. Both lines of code perform identical tasks. To show the fields again, simply set `Visible` to `True`. Alternatively, you can retrieve a `TColumn` object from the grid, and then quietly dispose of it:
    void __fastcall TForm1::HideCurrentColumn1Click(TObject *Sender)

    {

      if (MessageBox(Handle, "Hide Column?",

           "Hide Info?", MB_YESNO | MB_ICONQUESTION) == ID_YES)

      {

        TColumn *Column = DBGrid1->Columns->Items[DBGrid1->SelectedIndex];

        Column->Free();

      }

    }
That'll do it! The column disappears from the grid once it has been freed. Alternatively, you can set the width of a column to `0`, which makes the column itself go away, but not the lines between columns. In order to allow the user to decide which fields are visible, GridTricks pops up a second form with a series of checkboxes on it. The program actually creates each of these checkboxes at runtime. In other words, it doesn't just pop up a form with the correct number of checkboxes on it, but instead iterates through the `TblOrders` object, finds out how many checkboxes are needed, and then creates them dynamically at runtime. To perform these tasks, GridTricks calls on a form that is specially designed to display the checkboxes:
    void __fastcall TForm1::ShowFieldEditor1Click(TObject *Sender)

    {

      ColumnEditor->ShowColumns();

      DMod->tblOrders->Refresh();

    }
The `ShowColumns` method of the `VisiForm` first calls a routine called `CreateCheckBox` that creates the checkboxes, displays the form, and finally sets the state of the checkboxes:
    void TColumnEditor::ShowColumns(void)

    {

      int i;

      TColumn *Column;

      for (i = 0; i < DMod->OrdersTable->FieldCount; i++)

        CreateCheckBox(i, DMod->OrdersTable->Fields[i]->Name,

          DMod->OrdersTable->Fields[i]->Visible);

      Height = (DMod->OrdersTable->FieldCount - 1) * (CheckBoxAry[0]->Height + GAP);

      if (Height > 470)

        Height = 470;

      ShowModal();

      // This is the code that does not work in BCB 1.0

      for (i = 0; i < DMod->OrdersTable->FieldCount; i++)

        DMod->OrdersTable->Fields[i]->Visible = CheckBoxAry[i]->Checked;

    }
The `ShowColumns` method iterates through the `Query1` object and assigns one checkbox to each field. It also asks `TQuery` for the names of the fields, and determines whether or not each field is currently hidden or visible. Here is the code that creates a checkbox on-the-fly:
    void TColumnEditor::CreateCheckBox(int Index, AnsiString Name, BOOL Visible)

    {

      CheckBoxAry[Index] = (TCheckBox*) new TCustomCheckBox(this);

      CheckBoxAry[Index]->Parent = ColumnEditor;

      CheckBoxAry[Index]->Caption = Name;

      CheckBoxAry[Index]->Left = 10;

      CheckBoxAry[Index]->Top = Index * (CheckBoxAry[Index]->Height + GAP);

      CheckBoxAry[Index]->Width = 200;

      CheckBoxAry[Index]->Checked = Visible;

    }
Most of the code in this example is performing relatively mundane tasks such as assigning names and locations to the checkboxes. These are the two key lines:
    CheckBoxAry[Index] = (TCheckBox*) new TCustomCheckBox(this);

    CheckBoxAry[Index]->Parent = ColumnEditor;
The first line actually creates the checkbox and gives it an owner. The second line assigns a parent to the checkbox. NOTE: The difference between a parent and an owner can be confusing at times. A form is always the owner of the components that reside inside it. As such, it is responsible for allocating and deallocating memory for these components. A form might also be the parent of a particular component, which means that Windows will ensure the component will be displayed directly on the form. However, one component might also find that another component is its parent, even though both components are owned by the form. For instance, if you place a `TPanel` on a form and then two `TButtons` on the `TPanel`, all three components will be owned by the form; however, the buttons will have the panel as a parent, whereas the `TPanel` will have the form as a parent. Ownership has to do with memory allocation. Parenthood usually describes what surface a component will be displayed on. Ownership is a BCB issue--parenthood is mostly a Windows API issue. In particular, it's Windows that cares about parenting, and it's Windows that handles that actual drawing of the controls. If you get confused about this while in the midst of a lengthy programming session, you can look it up in the online help by searching on the topic Parent. The grids supplied with the first version of BCB are reasonably flexible objects that perform most of the tasks required of them. If you feel you need some additional functionality, check with third-party tool makers such as TurboPower software. A number of third-party grids with extended capabilities are available on the market, and some of them are well worth the purchase price. New features of the `TDuBGrid` object not found in BCB 1.0 include the capability to add combo boxes to the grid, and to color the columns of your grids. #### Moving Columns at Runtime To move the location of a column at runtime, you can simply change its index, which is a zero-based number:
    DMod->OrdersTable->FieldByName("CustNo")->Index = 0;

    DMod->OrdersTable->FieldByName("CustNo")->Index = 2;
By default, the `CustNo` field in the `Orders` table is at the second position, which means its index is `1`. The code in the first example moves it to the first position, whereas the code that reads `Query1CustNo.Index = 2;` moves it to the third position. Remember, the `Index` field is zero-based, so moving a field to `Index 1` moves it to the second field in a record. The first field is at `Index 0`. When you change the index of a field, you do not need to worry about the indexes of the other fields in a record; they will be changed automatically at runtime. That is all I'm going to say about `DBGrid` objects. I've gone on at considerable length about this one component, but this is one of the tools that lie at the heart of many database programs, and it's therefore worthy of a fairly serious look. ### Multirecord Objects Another object that deserves mention is the `TDBCtrlGrid`, shown in Figure 11.11. You can use this object to view multiple records from a single table at one time without using the `TDBGrid` component. In other words, you can drop down `TDBEdit` controls onto a `TDBCtrlGrid`, and these edit controls will automatically be duplicated in a series of rows, where the first set of controls shows the first record, the second set the second record, and so on. You only have to drop down one set of controls--the extra sets are duplicated for you automatically by the `DBCtrlGrid`. To get started with this object, drag and drop the `Country` table off the Explorer. Delete the `TDBGrid` object created for you automatically by BCB, and add the `TDBCtrlGrid` object off the Data Controls page of the Component Palette. Use the Fields Editor to drag and drop all the fields from `Country` table onto the top section of the `TDBCtrlGrid`. Arrange them as shown in Figure 11.11. If you need help getting everything arranged properly, notice that `TDBCtrlGrid`s have `RowCount` and `ColCount` properties that enable you to define the number of rows and columns in the object. In this case, I have set the `RowCount` to `7`. FIGURE 11.11. The `TDBCtrlGrid` object on the form of the `CtrlGrid` application. When displaying a grid on a form, it often helps to add a `DBNavigator` control to the form so the user can easily iterate through records without using the scrollbar. I like to place the `DBNavigator` on a panel at the bottom of the form. When arranging the grid and other components on the form at runtime, it sometimes helps to respond to the `OnResize` event for the form:
    void __fastcall TForm1::FormResize(TObject *Sender)

    {

      Grid->Left = 0;

      Grid->Top = 0;

      Grid->Width = ClientWidth;

      Grid->Height = ClientHeight - Panel1->Height;

    }
The code shown here arranges the grid so that it reaches from the top left of the form, all the way to the right of the form and down to the top of the panel on which the `DBNavigator` resides. The preceding few simple lines of code will automatically be called whenever the form is resized by the user, thereby guaranteeing that all the components stay properly arranged. The `DBCtrlGrid` component doesn't bring any new functionality to BCB. It's useful, however, because it eliminates the need to have the user slide the scrollbar back and forth on a `TDBGrid` object. In other words, the `TDBGrid` object sometimes forces you to use the scrollbar in order to view all the fields of a record. That can be inconvenient, but the ability to view multiple records at once is so valuable that users are willing to put up with the minor annoyance of the scrollbar. The point of the `TDBCtrlGrid` object is that it lets you view multiple records at one time, while eliminating the need to scroll back and forth when viewing the data. It's hardly earth- shattering in its importance, but it can be very useful under some circumstances. It's a way to make the presentation of your data potentially more viable to the user. ### Summary In this chapter you learned some fairly sophisticated methods for displaying the data from multiple tables. In particular, you saw how BCB handles the key features of a relational database. The tools discussed in this chapter include the following: The Fields Editor The Query by Example tool in the DBD The components discussed in this chapter include the following: `TField` descendant objects The `TDBCtrlGrid`, `TDBLookupComboBox`, and `TDBLookupListBox` objects The `DBGrid` and `DBCtrlGrid` components The properties discussed in this chapter include the following: Calculated fields Lookup fields Good database programmers will find that there is a considerable amount of hidden power in the `TField` object and in the Fields Editor, as well as the other tools and components mentioned in this chapter.
| ©Copyright, Macmillan Computer Publishing. All rights reserved.